package com.gxg.dao;

import com.gxg.entities.Course;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

@Repository
public class CourseDao
{
  @Value("${teacher.course.page.amount}")
  int teacherCoursesPageAmount;
  @Autowired
  private JdbcTemplate jdbcTemplate;
  
  public void createCourse(String id, String name, String tab, String decription, String img, String teacher, Timestamp createTime, Timestamp modificationTime)
  {
    String sql = "insert into Course values(?, ?, ?, ?, ?, ?, ?, ?)";
    this.jdbcTemplate.update(sql, new Object[] { id, name, tab, decription, img, teacher, createTime, modificationTime });
  }
  
  public int getCoursesCountByTeacherAndPage(String teacher, int page)
  {
    String sql = "select count(*) from Course where teacher=? order by modificationTime limit ?, ?";
    int rowCount = ((Integer)this.jdbcTemplate.queryForObject(sql, Integer.class, new Object[] { teacher, Integer.valueOf((page - 1) * this.teacherCoursesPageAmount), Integer.valueOf(this.teacherCoursesPageAmount) })).intValue();
    return rowCount;
  }
  
  public List<Course> getCoursesByTeacherAndPage(String teacher, int page)
  {
    if (getCoursesCountByTeacherAndPage(teacher, page) == 0) {
      return null;
    }
    String sql = "select * from Course where teacher=? order by modificationTime desc limit ?, ?";
    
    List<Course> courseList = this.jdbcTemplate.query(sql, new RowMapper()
    {
      public Course mapRow(ResultSet resultSet, int i)
        throws SQLException
      {
        Course course = new Course();
        course.setId(resultSet.getString("id"));
        course.setName(resultSet.getString("name"));
        course.setTab(resultSet.getString("tab"));
        course.setDescription(resultSet.getString("description"));
        course.setImg(resultSet.getString("img"));
        course.setTeacher(resultSet.getString("teacher"));
        course.setCreateTime(resultSet.getTimestamp("createTime"));
        course.setModificationTime(resultSet.getTimestamp("modificationTime"));
        return course;
      }
    }, new Object[] { teacher, 
    
      Integer.valueOf((page - 1) * this.teacherCoursesPageAmount), Integer.valueOf(this.teacherCoursesPageAmount) });
    return courseList;
  }
  
  public int getCourseCountById(String id)
  {
    String sql = "select count(*) from Course where id=?";
    int rowCount = ((Integer)this.jdbcTemplate.queryForObject(sql, Integer.class, new Object[] { id })).intValue();
    return rowCount;
  }
  
  public Course getCourseById(String id)
  {
    if (getCourseCountById(id) == 0) {
      return null;
    }
    String sql = "select * from Course where id=?";
    Course course = (Course)this.jdbcTemplate.queryForObject(sql, new RowMapper()
    {
      public Course mapRow(ResultSet resultSet, int i)
        throws SQLException
      {
        Course course = new Course();
        course.setId(resultSet.getString("id"));
        course.setName(resultSet.getString("name"));
        course.setTab(resultSet.getString("tab"));
        course.setDescription(resultSet.getString("description"));
        course.setImg(resultSet.getString("img"));
        course.setTeacher(resultSet.getString("teacher"));
        course.setCreateTime(resultSet.getTimestamp("createTime"));
        course.setModificationTime(resultSet.getTimestamp("modificationTime"));
        return course;
      }
    }, new Object[] { id });
    
    return course;
  }
  
  public int getCoursesCountByTeacher(String teacher)
  {
    String sql = "select count(*) from Course where teacher=?";
    int rowCount = ((Integer)this.jdbcTemplate.queryForObject(sql, Integer.class, new Object[] { teacher })).intValue();
    return rowCount;
  }
  
  public List<Course> getCoursesTop5ByTeacher(String teacher)
  {
    if (getCoursesCountByTeacher(teacher) == 0) {
      return null;
    }
    String sql = "select * from Course where teacher=? order by modificationTime desc limit 0, 5";
    List<Course> courseList = this.jdbcTemplate.query(sql, new RowMapper()
    {
      public Course mapRow(ResultSet resultSet, int i)
        throws SQLException
      {
        Course course = new Course();
        course.setId(resultSet.getString("id"));
        course.setName(resultSet.getString("name"));
        course.setTab(resultSet.getString("tab"));
        course.setDescription(resultSet.getString("description"));
        course.setImg(resultSet.getString("img"));
        course.setTeacher(resultSet.getString("teacher"));
        course.setCreateTime(resultSet.getTimestamp("createTime"));
        course.setModificationTime(resultSet.getTimestamp("modificationTime"));
        return course;
      }
    }, new Object[] { teacher });
    
    return courseList;
  }
  
  public void updateModificationTimeById(Timestamp time, String id)
  {
    String sql = "update Course set modificationTime=? where id=?";
    this.jdbcTemplate.update(sql, new Object[] { time, id });
  }
  
  public void updateNameAndDescriptionAndModificationTimeById(String id, String name, String description, Timestamp time)
  {
    String sql = "update Course set name=?, description=?, modificationTime=? where id=?";
    this.jdbcTemplate.update(sql, new Object[] { name, description, time, id });
  }
  
  public void deleteCourseById(String id)
  {
    String sql = "delete from Course where id=?";
    this.jdbcTemplate.update(sql, new Object[] { id });
  }
  
  public int getCoursesCount()
  {
    String sql = "select count(*) from Course";
    int rowCount = ((Integer)this.jdbcTemplate.queryForObject(sql, Integer.class)).intValue();
    return rowCount;
  }
  
  public List<Course> getCoursesTopNumber(int number)
  {
    if (getCoursesCount() == 0) {
      return null;
    }
    String sql = "select * from Course order by modificationTime desc limit 0, ?";
    List<Course> courseList = this.jdbcTemplate.query(sql, new RowMapper()
    {
      public Course mapRow(ResultSet resultSet, int i)
        throws SQLException
      {
        Course course = new Course();
        course.setId(resultSet.getString("id"));
        course.setName(resultSet.getString("name"));
        course.setTab(resultSet.getString("tab"));
        course.setDescription(resultSet.getString("description"));
        course.setImg(resultSet.getString("img"));
        course.setTeacher(resultSet.getString("teacher"));
        course.setCreateTime(resultSet.getTimestamp("createTime"));
        course.setModificationTime(resultSet.getTimestamp("modificationTime"));
        return course;
      }
    }, new Object[] {
    
      Integer.valueOf(number) });
    return courseList;
  }
  
  public int getCoursesCountByTab(String tab)
  {
    String sql = "select count(*) from Course where tab=?";
    int rowCount = ((Integer)this.jdbcTemplate.queryForObject(sql, Integer.class, new Object[] { tab })).intValue();
    return rowCount;
  }
  
  public List<Course> getCoursesByLimit(int limitStart, int limitStop)
  {
    if (getCoursesCount() == 0) {
      return null;
    }
    String sql = "select * from Course order by modificationTime desc limit ?, ?";
    List<Course> courseList = this.jdbcTemplate.query(sql, new RowMapper()
    {
      public Course mapRow(ResultSet resultSet, int i)
        throws SQLException
      {
        Course course = new Course();
        course.setId(resultSet.getString("id"));
        course.setName(resultSet.getString("name"));
        course.setTab(resultSet.getString("tab"));
        course.setDescription(resultSet.getString("description"));
        course.setImg(resultSet.getString("img"));
        course.setTeacher(resultSet.getString("teacher"));
        course.setCreateTime(resultSet.getTimestamp("createTime"));
        course.setModificationTime(resultSet.getTimestamp("modificationTime"));
        return course;
      }
    }, new Object[] {
    
      Integer.valueOf(limitStart), Integer.valueOf(limitStop) });
    return courseList;
  }
  
  public List<Course> getCoursesByTabAndLimit(String tab, int limitStart, int limitStop)
  {
    if (getCoursesCount() == 0) {
      return null;
    }
    String sql = "select * from Course where tab=? order by modificationTime desc limit ?, ?";
    List<Course> courseList = this.jdbcTemplate.query(sql, new RowMapper()
    {
      public Course mapRow(ResultSet resultSet, int i)
        throws SQLException
      {
        Course course = new Course();
        course.setId(resultSet.getString("id"));
        course.setName(resultSet.getString("name"));
        course.setTab(resultSet.getString("tab"));
        course.setDescription(resultSet.getString("description"));
        course.setImg(resultSet.getString("img"));
        course.setTeacher(resultSet.getString("teacher"));
        course.setCreateTime(resultSet.getTimestamp("createTime"));
        course.setModificationTime(resultSet.getTimestamp("modificationTime"));
        return course;
      }
    }, new Object[] { tab, 
    
      Integer.valueOf(limitStart), Integer.valueOf(limitStop) });
    return courseList;
  }
}
